-- 创建作业表
CREATE TABLE homeworks (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    title VARCHAR(200) NOT NULL,
    description TEXT,
    subject VARCHAR(50) NOT NULL,  -- 学科：数学、英语、物理等
    class_id VARCHAR(50) NOT NULL,  -- 班级ID
    teacher_id VARCHAR(36) NOT NULL REFERENCES users(id),
    deadline TIMESTAMP WITH TIME ZONE NOT NULL,  -- 截止时间
    status VARCHAR(20) NOT NULL DEFAULT 'active',  -- active, closed, grading, completed
    questions JSONB,  -- 题目列表（可选）
    qrcode_url VARCHAR(500),  -- 二维码URL
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    
    -- 约束
    CONSTRAINT homeworks_status_check CHECK (status IN ('active', 'closed', 'grading', 'completed')),
    CONSTRAINT homeworks_deadline_check CHECK (deadline > created_at)
);

-- 创建索引（优化查询性能）
CREATE INDEX idx_homeworks_class_id ON homeworks (class_id);
CREATE INDEX idx_homeworks_teacher_id ON homeworks (teacher_id);
CREATE INDEX idx_homeworks_subject ON homeworks (subject);
CREATE INDEX idx_homeworks_status ON homeworks (status);
CREATE INDEX idx_homeworks_deadline ON homeworks (deadline);

-- 创建作业提交表
CREATE TABLE homework_submissions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    homework_id UUID NOT NULL REFERENCES homeworks(id) ON DELETE CASCADE,
    student_id VARCHAR(36) NOT NULL REFERENCES users(id),
    image_urls JSONB NOT NULL,  -- 图片URL数组
    status VARCHAR(20) NOT NULL DEFAULT 'uploaded',  -- uploaded, grading, graded, reviewed
    submitted_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    
    -- 约束：每个学生每个作业只能提交一次
    CONSTRAINT homework_submissions_unique UNIQUE (homework_id, student_id),
    CONSTRAINT homework_submissions_status_check CHECK (status IN ('uploaded', 'grading', 'graded', 'reviewed'))
);

-- 创建索引
CREATE INDEX idx_submissions_homework_id ON homework_submissions (homework_id);
CREATE INDEX idx_submissions_student_id ON homework_submissions (student_id);
CREATE INDEX idx_submissions_status ON homework_submissions (status);

-- 触发器：自动更新 updated_at
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER update_homeworks_updated_at BEFORE UPDATE ON homeworks
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_homework_submissions_updated_at BEFORE UPDATE ON homework_submissions
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- 添加注释
COMMENT ON TABLE homeworks IS '作业表';
COMMENT ON TABLE homework_submissions IS '作业提交表';
COMMENT ON COLUMN homeworks.questions IS '题目列表，JSONB格式存储';
COMMENT ON COLUMN homework_submissions.image_urls IS '作业图片URL数组，JSONB格式存储';

